﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using QuanLyDoanVien.General;
using System.Data;
using System.Data.SqlClient;
using QuanLyDoanVien.Bussiness;

namespace QuanLyDoanVien.DataLayer
{
    class LopData
    {
        DataService m_LopData = new DataService();
        public List<LopInfo> lay_DSLop()
        {
            List<LopInfo> rs = new List<LopInfo>();
            DataService.OpenConnection();
            SqlCommand cmd = new SqlCommand("SELECT * FROM Tab_Lop");
            m_LopData.Load(cmd);
            foreach (DataRow dr in m_LopData.Rows)
            {
                LopInfo l = new LopInfo();
                l.MaLop = dr["MaLop"].ToString();
                l.TenLop = dr["TenLop"].ToString();
                l.Chuyen = dr["Chuyen"].ToString();
                rs.Add(l);
            }
            m_LopData.CloseConnection();
            return rs;
        }
       
        public DataTable LayDSLop()
        {
            SqlCommand cmd = new SqlCommand("SELECT * FROM Tab_Lop LP INNER JOIN Tab_HocSinh HS ON HS.MaLop = LP.MaLop");
            m_LopData.Load(cmd);
            return m_LopData;
        }
        public void ThemLop(DataRow row)
        {
            m_LopData.Rows.Add(row);
    
        }

        #region Them Lop
        public bool LuuLop()
        {
            return m_LopData.ExecuteNoneQuery() > 0;
        }

        public void LuuLop(LopInfo lop)
        {
            SqlCommand cmd = new SqlCommand("INSERT INTO Tab_Lop VALUES(@MaLop, @TenLop, @Chuyen)");
            cmd.Parameters.Add("MaLop", SqlDbType.Char).Value = lop.MaLop;
            cmd.Parameters.Add("TenLop", SqlDbType.NVarChar).Value = lop.TenLop;
            cmd.Parameters.Add("Chuyen", SqlDbType.NVarChar).Value = lop.Chuyen;
      
            m_LopData.Load(cmd);
        }
        #endregion

        #region Sua
        public bool SuaLop(LopInfo l)
        {
            DataService.OpenConnection();
            SqlCommand cmd = new SqlCommand("UPDATE Tab_Lop SET TenLop = @TenLop, Chuyen = @Chuyen WHERE MaLop = @MaLop");
            cmd.Parameters.Add("MaLop", SqlDbType.Char).Value = l.MaLop;
            cmd.Parameters.Add("TenLop", SqlDbType.NVarChar).Value = l.TenLop;
            cmd.Parameters.Add("Chuyen", SqlDbType.NVarChar).Value = l.Chuyen;
            m_LopData.Load(cmd);
            try
            {
                m_LopData.ExecuteNoneQuery(cmd);
                m_LopData.CloseConnection();
                return true;
            }
            catch
            {
                m_LopData.CloseConnection();
                return false;
            }


        }
        #endregion

        #region Xoa
        public bool XoaLop(String malop)
        {
            DataService.OpenConnection();
            SqlCommand cmd = new SqlCommand("DELETE FROM Tab_Lop WHERE MaLop = @MaLop");
            cmd.Parameters.Add("MaLop", SqlDbType.Char).Value = malop;
            //m_LopData.Load(cmd);
            try
            {
                m_LopData.ExecuteNoneQuery(cmd);
                m_LopData.CloseConnection();
                return true;
            }
            catch
            {
                m_LopData.CloseConnection();
                return false;
            }
        }

        #endregion


        public string getAutoID()
        {
            DataService.OpenConnection();
            QuyDinh qd = new QuyDinh();
            string id = "";
            string sql = "select top 1 MaLop from Tab_Lop Order By MaLop desc";
            SqlCommand command = new SqlCommand(sql);
            m_LopData.Load(command);
            if (m_LopData.Rows.Count < 1)
                return "LP00000001";
            id = m_LopData.Rows[0][0].ToString();
            int num = int.Parse(id.Substring(2, 8)) + 1;
            id = "LP" + qd.LaySTT(num);
            m_LopData.CloseConnection();
            return id;
        }
    }
}
